﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.OleDb;

using NHibernate;
using BEGDC.Utility.NHibernate;

using BEGDC.Office.Interface;
using BEGDC.Office.Library;

namespace BEGDC.MIS.Office.Excel
{
    public abstract class StandardExcelService : ExcelService
    {
        private const string EXCEL_CONNECTION = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\";Data Source=";

        public StandardExcelService()
        {
            definition = XmlDefinition.CreateInstance(ServiceName);
        }

        private XmlDefinition definition;
        protected virtual XmlDefinition Definition
        {
            get
            {
                return definition;
            }
        }
        
        
        public override DataSet GetDataFromFile(string fileName)
        {
            DataSet result;

            if (definition == null)
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0001", "Definition isn't found.");

            if (!System.IO.File.Exists(fileName))
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0002", "Tagert office file isn't found.");

            try
            {
                result = new DataSet();

                using (OfficeSession session = SessionFactory.OpenSession(OfficeSessionType.Excel))
                {
                    IExcelSession excel_session = (IExcelSession)session;

                    if (excel_session == null)
                        throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0004", "Session can't be created, the excel service is busy now.");

                    using (OleDbConnection con = new OleDbConnection(EXCEL_CONNECTION + fileName))
                    {
                        con.Open();

                        for (int i = 0; i < definition.Tables.Count; i++)
                        {
                            XmlTable xml_table = definition.Tables[i];

                            string sql = "SELECT ";

                            //if (xml_table.Rows.Count > 0)
                            //{
                            //    XmlRow xml_row = xml_table.Rows[0];
                            //    for (int k = 0; k < xml_row.Elements.Count; k++)
                            //    {
                            //        Element element = xml_row.Elements[k];
                            //        sql += element.Position + " As " + element.Name + " , ";
                            //    }
                            //}

                            if (sql != "SELECT ")
                            {
                                sql = sql.Substring(0, sql.Length-2);
                            }
                            else
                            {
                                sql = "SELECT * ";
                            }


                            OleDbCommand cmd = new OleDbCommand(sql + "FROM [" + xml_table.Name + "$]", con);
                            OleDbDataAdapter da = new OleDbDataAdapter();

                            da.SelectCommand = cmd;
                            da.Fill(result, xml_table.Name);

                            //validation
                            base.Validate(xml_table, result.Tables[xml_table.Name]);
                        }
                        con.Close();
                    }
                }
                return result;
            }
            catch (BEGDC.Utility.ExceptionManagement.GDCException gdce)
            {
                throw gdce;
            }
            catch (Exception err)
            {
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0003", "Error is found when reading data from excel file." + err.Message , err);
            }
        }

        public override void SaveDataToFile(DataSet saveData, string fileName)
        {
            if (definition == null)
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0001", "Definition isn't found.");

            if (!System.IO.File.Exists(fileName))
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0002", "Tagert office file isn't found.");

            try
            {
                using (OfficeSession session = SessionFactory.OpenSession(OfficeSessionType.Excel))
                {
                    IExcelSession excel_session = (IExcelSession)session;

                    if (excel_session == null)
                        throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0004", "Session can't be created, the excel service is busy now.");

                    using (OleDbConnection con = new OleDbConnection(EXCEL_CONNECTION + fileName))
                    {
                        con.Open();

                        for (int i = 0; i < definition.Tables.Count; i++)
                        {
                            XmlTable xml_table = definition.Tables[i];

                            DataTable table = saveData.Tables[xml_table.Name];

                            for (int j = 0; j < table.Rows.Count; j++)
                            {
                                DataRow row = table.Rows[j];
                                string sql = "Insert Into [" + xml_table.Name + "$] (";

                                string vsql = ") Values (";

                                if (xml_table.Rows.Count > 0)
                                {
                                    XmlRow xml_row = xml_table.Rows[0];
                                    for (int k = 0; k < xml_row.Elements.Count; k++)
                                    {
                                        Element element = xml_row.Elements[k];

                                        sql += element.Name + ",";
                                        vsql += GetSqlByType(row[element.Name].ToString(), element.Type) + ",";
                                    }
                                    sql = sql.Substring(0, sql.Length - 1) + vsql.Substring(0, vsql.Length - 1) + ")";

                                    OleDbCommand cmd = new OleDbCommand(sql, con);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                        con.Close();
                    }
                }
            }
            catch (BEGDC.Utility.ExceptionManagement.GDCException gdce)
            {
                throw gdce;
            }
            catch (Exception err)
            {
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0003", "Error is found when reading data from excel file.", err);
            }
        }

        private string GetSqlByType(string strValue, string strType)
        {
            string value = strValue;
            try
            {
                switch (strType.ToLower())
                {
                    case "int":
                        value = strValue;
                        break;
                    case "long":
                        value = strValue;
                        break;
                    case "decimal":
                        value = strValue;
                        break;
                    case "datatime":
                        value = "'" + strValue + "'";
                        break;
                    case "bool":
                        if ((strValue.ToLower() == "true") || (strValue.ToLower() == "y") || (strValue.ToLower() == "是"))
                            value = "1";
                        else
                            value = "0";
                        break;
                    default:
                        value = "'" + strValue + "'";
                        break;
                }
                return value;
            }
            catch (Exception err)
            {
                throw new BEGDC.Utility.ExceptionManagement.GDCException("00.0000", "Excel data format isn't correct.", err);
            }
        }
    }
}
